import os
import sqlite3
import sys
import pandas as pd
from pandas import DataFrame
from src.backend.mydecorator import bean
from concurrent.futures import ThreadPoolExecutor

@bean(id='db')
class SQLite:

    def __init__(self,db,handout_dir,word_path,image_path):
        self.conn=sqlite3.connect(db)
        self.cursor=self.conn.cursor()
        self.handout_dir=handout_dir
        self.word_path=word_path
        self.image_path=image_path

    @property
    def _cursor(self):
        return self.cursor

    def init_db(self):
        self.cursor.executescript('''
                    CREATE TABLE student
                    ( name text, age integer,grade text);
                    CREATE TABLE "topic" (
  "content" text DEFAULT 资源内容,
  "methods" text,
  "modules" text DEFAULT 关键字,
  "id" INTEGER NOT NULL,
  "answer" TEXT DEFAULT 答案,
  "examinations" TEXT,
  "method" TEXT,
  "level" integer DEFAULT 资源困难度,
  "category" TEXT DEFAULT 资源类型,
  "score" real,
  PRIMARY KEY ("id")
);
CREATE TABLE "correcting" (
  "topic_pos" text,
  "student" TEXT,
  "date" DATE,
  "right" integer
);
CREATE TABLE knowledge
    (id integer primary key,name text, parent_id integer,book integer,level integer,temp1 text,temp2 text);
                    CREATE TABLE "time_period" (
  "id" INTEGER NOT NULL,
  "name" TEXT,
  PRIMARY KEY ("id")
);
CREATE TABLE "topic_level" (
  "id" INTEGER NOT NULL,
  "name" TEXT,
  PRIMARY KEY ("id")
);
INSERT INTO "topic_level" ("name") VALUES ('简单');
INSERT INTO "topic_level" ("name") VALUES ('中档');
INSERT INTO "topic_level" ("name") VALUES ('较难');
INSERT INTO "topic_level" ("name") VALUES ('困难');
CREATE TABLE "type" (
  "id" INTEGER NOT NULL,
  "name" TEXT,
  PRIMARY KEY ("id")
);
INSERT INTO "type" ("name") VALUES ('单选');
INSERT INTO "type" ("name") VALUES ('多选');
INSERT INTO "type" ("name") VALUES ('填空');
INSERT INTO "type" ("name") VALUES ('解答');
CREATE TABLE "classroom" (
  "date" DATE,
  "time_period" integer,
  "student" TEXT,
  "id" INTEGER NOT NULL,
  PRIMARY KEY ("id")
);
CREATE TABLE publish_history
( file_path text, student text,date integer, knowledge text)
                    ''')
        self.conn.commit()

    def insert(self,object):
        if isinstance(object,list):
            attr = object[0].__combineAttr__()
            values=','.join(['?']*len(object[0].__dict__))
            sql = f"""
                        INSERT INTO {object[0].__table__()} ({attr})
                        VALUES ({values})
                        """
            attrs = list(vars(object[0]).keys())
            params = [tuple(getattr(ob, attr) for attr in attrs) for ob in object]
            self.cursor.executemany(sql, params)
        else:
            attr = object.__combineAttr__()
            values = ','.join(['?'] * len(object.__dict__))
            sql = f"""
            INSERT INTO {object.__table__()} ({attr})
            VALUES ({values})
            """
            attrs=list(vars(object).keys())
            params=tuple(getattr(object, attr) for attr in attrs)
        # 假设 self.cursor 是一个有效的 SQLite 游标对象
            self.cursor.execute(sql,params)
        self.conn.commit()

    def query(self,sql,params=None)->DataFrame:
        if params:
            results = pd.read_sql_query(sql, self.conn,params=params)
        else:
            results=pd.read_sql_query(sql,self.conn)
        return results

    def delete(self,sql):
        self.cursor.execute(sql)
        self.conn.commit()


class Sqlalchemy:
    # 创建数据库引擎

    def __init__(self,db):
        engine = create_engine(db)
        # 创建表
        Base.metadata.create_all(engine)
        # 创建会话
        Session = sessionmaker(bind=engine)
        self.session = Session()

    def add(self,obj):
        pass


